<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//insert section
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO section VALUES (?, ?, ?)");
							pstmt.setInt(1, Integer.parseInt(request.getParameter("class_id")));   
							pstmt.setInt(2, Integer.parseInt(request.getParameter("limit"))); 
							pstmt.setString(3, request.getParameter("faculty_name")); 	
	
					        pstmt.executeUpdate();  
					        
		        
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							try{
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE section SET limit = ?, faculty_name = ? WHERE section_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("limit")));   	
	                        pstmt.setString(2, request.getParameter("faculty_name"));  	
							pstmt.setInt(3, Integer.parseInt(request.getParameter("section_id"))); 
							
	                        pstmt.executeUpdate();
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
							} catch (SQLException e) {
								out.println(e.getMessage());
								conn.rollback();
							}
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        //String section_id = request.getParameter("section_id");
	                      	//delete meeting
	                      	PreparedStatement pstmt = conn.prepareStatement(
	                            "DELETE FROM meeting WHERE section_id = ?");
	                      	pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete review session
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM review_session WHERE section_id = ?");
	                      	pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete enroll
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM enroll WHERE section_id = ?");
	                      	pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));
	                      	pstmt.executeUpdate();
	                      	
	                      	//delete section
	                      	pstmt = conn.prepareStatement(
	                            "DELETE FROM section WHERE section_id = ?");
	                      	pstmt.setInt(1, Integer.parseInt(request.getParameter("section_id")));
	                      	pstmt.executeUpdate();
	                      	
	                      	conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						
						
						//obtain all the class information 
						ResultSet rsClass = statement.executeQuery("SELECT * FROM class ORDER BY year");
						CachedRowSet cachedClass = new CachedRowSetImpl();
						cachedClass.populate(rsClass);
						
						//obtain the faculty information
						ResultSet rsFaculty = statement.executeQuery("SELECT name FROM faculty");
						List<String> faculty = new LinkedList<String>();
						while(rsFaculty.next()){
							//System.out.println("YEP!!");
							faculty.add(rsFaculty.getString("name"));
						}
						//obtain the current section information	
						ResultSet rsSection = statement.executeQuery("SELECT * FROM section");
						CachedRowSet cachedSection = new CachedRowSetImpl();
						cachedSection.populate(rsSection);
						
				%>
				<table border="1">
					<tr>
						<th>Section ID</th>
						<th>Class</th>			
						<th>Limit</th>
						<th>Instructor</th>
					</tr>
					<tr>
						<form action="section.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th></th>
							<th>
								<select name="class_id" style="width:200px">		
								<%	
									while (cachedClass.next()) {
										int class_id = cachedClass.getInt("class_id");
										String course_number = cachedClass.getString("course_number");
										String title = cachedClass.getString("title");
										String quarter = cachedClass.getString("quarter");
										int year = cachedClass.getInt("year");
								%>
									<option value="<%=class_id%>">
										<%=course_number%> <%=title%> (<%=quarter%> <%=year%>)
									</option>
								<%
									}
								%>
								</select>
							</th>
							<th><input type="text" value="" name="limit" size="10"></th>
							<th>
								<select name="faculty_name" style="width:200px">
								<%
									for(String instructor: faculty){
								%>
										<option value="<%=instructor%>"><%=instructor%></option>
								<%
									}
								%>
								</select>
							</th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
							<% 
                       			while(cachedSection.next()){		
                           	%> 
                				<tr>	
                   				<form action="section.jsp" method="POST">
                      				<input type="hidden" value="update" name="action">                        
                       			<input type="hidden" value="<%=cachedSection.getInt("section_id")%>" name="section_id">
                       			<td><%= cachedSection.getInt("section_id") %></td>
                       			<%
                       				PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM class WHERE class_id = ?");
                       				pstmt.setInt(1, cachedSection.getInt("class_id"));
                       				ResultSet rs = pstmt.executeQuery();
                       				if(rs.next()){
                       					
       									String course_number = rs.getString("course_number");
       									String title = rs.getString("title");
       									String quarter = rs.getString("quarter");
       									int year = rs.getInt("year");
                       			%>
                       			<td> 
                       				<%=course_number%> <%=title%> (<%=quarter%> <%=year%>)
                       			</td>
                       			<%
                       				}
                       			%>
                       			
                       			<td><input type="text" value="<%= cachedSection.getString("limit")%>" name="limit" size="10"></td>
                       			
                       			<td>
                       				<select name="faculty_name" style="width:200px">	
                       					<%
                       						for(String instructor: faculty){
                       					%>
                       							<option value="<%=instructor%>" 
                       							<%if(instructor.equals(cachedSection.getString("faculty_name"))) out.println("selected='selected'");%>>
                       							<%=instructor%>
                       							</option>
                       					<%
                       						}
                       					%>
                       				</select>
                       			</td>
                       			<td>
                           			<input type="submit" value="Update">
                       			</td>
                   				</form>
                   				
                   				<td>
                  					 <form action="section.jsp" method="POST">
                      					 <input type="hidden" value="delete" name="action">
                     					 <input type="hidden" value="<%= cachedSection.getInt("section_id") %>" name="section_id">                       			
                          				 <input type="submit" value="Delete">
                      				 </form>
                      				 </td>
                  
               				</tr>
                   			<% 
                           	}
                           	%>
					
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							//conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>